import os
import shutil

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import numbers
import datetime
import tools_pmc
import numpy as np
import statsmodels.api as sm
import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image


def tdl(table_array_wlxxb: list):
    def tdl1(sn1: str, sn2: str, st1: str, dict_tdlmx: dict):
        if sn1 in dict_tdlmx.keys() and sn2 in dict_tdlmx[sn1].keys():
            if "通用替代" in dict_tdlmx[sn1][sn2]:
                return
        if sn2 in dict_tdlmx[sn1]:
            if "*" in st1:
                dict_tdlmx[sn1][sn2] = ["通用替代"]
            else:
                dict_tdlmx[sn1][sn2].append(st1)
        else:
            if "*" in st1:
                dict_tdlmx[sn1][sn2] = ["通用替代"]
            else:
                dict_tdlmx[sn1][sn2] = [st1]

    def tdl2(sn: str):
        list_21 = []
        for ii in range(a, ws_bom.max_row + 1):
            if ws_bom.cell(row=ii, column=3).value == sn:
                list_21.append(ws_bom.cell(row=ii, column=1).value)
        return list_21

    def tdl3(sn: str):
        str_temp = ""
        if len(dict_tdlmx_t[sn].keys()) > 1:
            str_temp = "推荐替代料：\n"
        optimal = dict_tdlmx_t_pro[sn]["optimal"]
        str_temp += optimal + " 状态：" + dict_tdlmx_t_pro[sn][optimal]["status"] + " 库存：" + str(
            dict_tdlmx_t_pro[sn][optimal]["可用量"])
        str1_temp = ""
        str3_temp = ""
        for jj in dict_tdlmx_t[sn][optimal]:
            str2_temp = tools_pmc.vlookup(jj, table_array_wlxxb, 3)
            if str2_temp != str1_temp:
                str1_temp = str2_temp
                if jj == "通用替代" or jj == "替代BOM为N":
                    str_temp += "\n" + jj + "、"
                    str3_temp += "\n" + jj + "、"
                    continue
                str_temp += "\n" + str2_temp + ":" + jj + "、"
                str3_temp += "\n" + str2_temp + ":\n" + jj + "、"
            else:
                str_temp += jj + "、"
                str3_temp += jj + "、"
        dict_tdlmx_t_pro[sn][optimal]["string"] = str3_temp[1:-1]
        if len(dict_tdlmx_t[sn].keys()) > 1:
            str_temp += "\n\n其他替代料：\n"
            for ii in dict_tdlmx_t[sn]:
                if ii != optimal:
                    str_temp += ii + " 状态：" + dict_tdlmx_t_pro[sn][ii]["status"] + " 库存：" + str(
                        dict_tdlmx_t_pro[sn][ii]["可用量"])
                    str1_temp = ""
                    str3_temp = ""
                    for jj in dict_tdlmx_t[sn][ii]:
                        str2_temp = tools_pmc.vlookup(jj, table_array_wlxxb, 3)
                        if str2_temp != str1_temp:
                            str1_temp = str2_temp
                            if jj == "通用替代" or jj == "替代BOM为N":
                                str_temp += "\n" + jj + "、"
                                str3_temp += "\n\n" + jj + "、"
                                continue
                            str_temp += "\n" + str2_temp + ":" + jj + "、"
                            str3_temp += "\n\n" + str2_temp + ":\n" + jj + "、"
                        else:
                            str_temp += jj + "、"
                            str3_temp += jj + "、"
                    dict_tdlmx_t_pro[sn][ii]["string"] = str3_temp[2:-1]
                    str_temp += "\n"
        return str_temp[:-1]

    def select_material(sn: str):
        for ii in dict_tdlmx_t_pro[sn]:
            if dict_tdlmx_t_pro[sn][ii]['status'] == '优先消耗':
                if dict_tdlmx_t_pro[sn][ii]['可用量'] > 0:
                    return ii
        dict_tongyong = {}
        dict_other = {}
        for ii in dict_tdlmx_t_pro[sn]:
            if dict_tdlmx_t_pro[sn][ii]['status'] != '冻结':
                if dict_tdlmx_t[sn][ii] == ["通用替代"]:
                    dict_tongyong[ii] = dict_tdlmx_t_pro[sn][ii]['可用量']
                else:
                    dict_other[ii] = dict_tdlmx_t_pro[sn][ii]['可用量']
        if dict_tongyong:
            return max(dict_tongyong, key=dict_tongyong.get)
        elif dict_other:
            return max(dict_other, key=dict_other.get)
        else:
            return list(dict_tdlmx_t_pro[sn].keys())[0]

    def tdl3_vlookup_two(lookup_value1, lookup_value2, col_index_num):
        for row in table_array_tdlkb:
            if str(row[0].value) == str(lookup_value1) and str(row[5].value) == str(lookup_value2):
                return row[col_index_num - 1].value
        return "使用中*"

    def tdl4_str(listin: list):
        temp = ""
        for ii in listin:
            temp += ii + "、"
        return temp[:-1]

    def tdl5_dict_tdlmx(lj_tdlmx):  # 读取替代料管理表，返回处理后替代关系字典
        wb_tdlmx = openpyxl.load_workbook(lj_tdlmx)
        ws_tdlmx = wb_tdlmx.worksheets[0]
        dict_tdlmx_temp = {}
        for ii in range(2, ws_tdlmx.max_row + 1):
            if ws_tdlmx.cell(row=ii, column=1).value in dict_tdlmx_temp.keys():
                tdl1(ws_tdlmx.cell(row=ii, column=1).value, ws_tdlmx.cell(row=ii, column=7).value,
                     ws_tdlmx.cell(row=ii, column=4).value, dict_tdlmx_temp)
            else:
                dict_tdlmx_temp[ws_tdlmx.cell(row=ii, column=1).value] = {}
                tdl1(ws_tdlmx.cell(row=ii, column=1).value, ws_tdlmx.cell(row=ii, column=7).value,
                     ws_tdlmx.cell(row=ii, column=4).value, dict_tdlmx_temp)
        for ii in dict_tdlmx_temp.keys():
            for jj in dict_tdlmx_temp[ii].keys():
                list_temp_1 = []
                for sn_temp in dict_tdlmx_temp[ii][jj]:
                    if sn_temp == "通用替代":
                        list_temp_1 = ["通用替代"]
                        break
                    elif sn_temp[0] == "3":
                        list_temp_1.append(sn_temp)
                    else:
                        list_temp_1.extend(tdl2(sn_temp))
                list_temp_1 = list(set(list_temp_1))
                list_temp_1.sort()
                if not list_temp_1:
                    dict_tdlmx_temp[ii][jj] = ["替代BOM为N"]
                else:
                    dict_tdlmx_temp[ii][jj] = list_temp_1
        return dict_tdlmx_temp

    def tdl6_vlookup_wlxx(lookup_value1):
        for row in table_array_wlxxb:
            if str(row[0].value) == str(lookup_value1):
                return row[14].value, row[15].value - row[16].value, row[16].value, row[17].value

    wb_bom = openpyxl.load_workbook(tools_pmc.lj_bom)
    ws_bom = wb_bom.worksheets[0]
    #  加速优化代码
    a = 0
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(row=i, column=1).value[0] == "3":
            a = i
            break

    try:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/" + tools_pmc.today_str1 + "替代料管理看板.xlsx")
    except FileNotFoundError:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/" + tools_pmc.yesterday_str1 + "替代料管理看板.xlsx")
    ws = wb.worksheets[0]
    table_array_tdlkb = list(ws['A3':'O' + str(ws.max_row)])

    dict_tdlmx_t = tdl5_dict_tdlmx(tools_pmc.lj_tdlmx_t)
    dict_tdlmx_y = tdl5_dict_tdlmx(tools_pmc.lj_tdlmx_y)

    dict_tdlmx_t_pro = {}
    for i in dict_tdlmx_t.keys():  # 处理替代关系字典，添加库存和状态信息
        dict_tdlmx_t_pro[i] = {}
        for j in dict_tdlmx_t[i].keys():
            temp_list = tdl6_vlookup_wlxx(j)
            dict_tdlmx_t_pro[i][j] = {'可用量': temp_list[0],
                                      '在途': temp_list[1],
                                      '在检': temp_list[2],
                                      '已请未购': temp_list[3],
                                      'status': tdl3_vlookup_two(i, j, 9)}
    for i in dict_tdlmx_t_pro:  # 处理替代关系字典，添加最优替代物料
        dict_tdlmx_t_pro[i]["optimal"] = select_material(i)
        dict_tdlmx_t_pro[i]["string"] = tdl3(i)

    wb.remove(wb["替代关系看板"])
    wb.create_sheet("替代关系看板")
    ws = wb["替代关系看板"]
    wb.move_sheet(wb["替代关系变更台账"], 2)
    wb.move_sheet(wb["替代料库存看板"], 2)
    wb.active = ws
    dict_temp = {}
    a = 3
    for i in dict_tdlmx_t:
        for j in dict_tdlmx_t[i]:
            list_temp = [i,
                         tools_pmc.vlookup(i, table_array_wlxxb, 2),
                         tools_pmc.vlookup(i, table_array_wlxxb, 3),
                         tdl4_str(dict_tdlmx_t[i][j]),
                         dict_tdlmx_t_pro[i][j]["string"],
                         j,
                         tools_pmc.vlookup(j, table_array_wlxxb, 2),
                         tools_pmc.vlookup(j, table_array_wlxxb, 3),
                         dict_tdlmx_t_pro[i][j]["status"],
                         dict_tdlmx_t_pro[i][j]["可用量"],
                         dict_tdlmx_t_pro[i][j]["在途"],
                         dict_tdlmx_t_pro[i][j]["在检"],
                         dict_tdlmx_t_pro[i][j]["已请未购"],
                         tdl3_vlookup_two(i, j, 14),
                         tdl3_vlookup_two(i, j, 15),
                         ]
            for k in range(0, len(list_temp)):
                ws.cell(row=a, column=k + 1).value = list_temp[k]
            a += 1

    dict_temp = {"all": 0,
                 "优先消耗": 0,
                 "num": ws.max_row - 2}
    for i in range(3, ws.max_row + 1):
        dict_temp["all"] += ws.cell(row=i, column=10).value
        if ws.cell(row=i, column=9).value == "优先消耗":
            dict_temp["优先消耗"] += ws.cell(row=i, column=10).value

    list_title = ["原件品号", "原件品名", "原件规格", "替代关系（排产读取）", "替代关系", "替代料品号", "替代料品名",
                  "替代料规格", "当前状态", "可用数量", "在途数量", "在检数量", "已请未购", "备注一",
                  "备注二"]
    list_leng = [14, 26, 30, 14, 14, 14, 26, 30, 14, 10, 10, 10, 10, 45, 45]
    for i in range(1, len(list_leng) + 1):
        ws.cell(2, i).value = list_title[i - 1]
        ws.cell(2, i).fill = tools_pmc.fill_blue
        ws.column_dimensions[get_column_letter(i)].width = list_leng[i - 1]
    ws.cell(2, 10).fill = tools_pmc.fill_yellow
    ws.merge_cells("J1:M1")
    ws.cell(1, 10).value = "替代料"
    ws.column_dimensions['D'].hidden = True
    ws.cell(1, 1).value = "看板更新日期：" + tools_pmc.today_str1
    ws.auto_filter.ref = "A2:O" + str(ws.max_row)

    # 替代料品号留存
    data = []
    for i in range(3, ws.max_row + 1):
        data.append(ws.cell(i, 6).value)
    with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\tdl.pickle", "wb") as f:
        import pickle
        pickle.dump(data, f)

    ws = wb["替代关系变更台账"]  # 变更记录表
    a = ws.max_row + 2
    ws.cell(a, 1).value = "日期：" + tools_pmc.today_str1
    a += 1
    list_title = ["变更类型", "原件品号", "原件品名", "原件规格", "替代料品号",
                  "替代料品名", "替代料规格", "变更内容"]
    list_leng = [14, 14, 26, 30, 14, 26, 30, 30]
    for i in range(1, len(list_leng) + 1):
        ws.cell(a, i).value = list_title[i - 1]
        ws.cell(a, i).fill = tools_pmc.fill_blue
    a += 1
    for i in dict_tdlmx_y:
        for j in dict_tdlmx_y[i]:
            bool1 = True
            for k in dict_tdlmx_t:
                for l in dict_tdlmx_t[k]:
                    if i == k and j == l:
                        if dict_tdlmx_y[i][j] != dict_tdlmx_t[k][l]:
                            ws.cell(a, 1).value = "替代条件变更"
                            ws.cell(a, 2).value = i
                            ws.cell(a, 3).value = tools_pmc.vlookup(i, table_array_wlxxb, 2)
                            ws.cell(a, 4).value = tools_pmc.vlookup(i, table_array_wlxxb, 3)
                            ws.cell(a, 5).value = j
                            ws.cell(a, 6).value = tools_pmc.vlookup(j, table_array_wlxxb, 2)
                            ws.cell(a, 7).value = tools_pmc.vlookup(j, table_array_wlxxb, 3)
                            ws.cell(a, 8).value = ("增加：\n" +
                                                   tdl4_str(list(set(dict_tdlmx_t[k][l]) - set(dict_tdlmx_y[i][j]))) +
                                                   "\n减少：\n" +
                                                   tdl4_str(list(set(dict_tdlmx_y[i][j]) - set(dict_tdlmx_t[k][l]))))
                            a += 1
                        bool1 = False
            if bool1:
                ws.cell(a, 1).value = "替代关系删除"
                ws.cell(a, 2).value = i
                ws.cell(a, 3).value = tools_pmc.vlookup(i, table_array_wlxxb, 2)
                ws.cell(a, 4).value = tools_pmc.vlookup(i, table_array_wlxxb, 3)
                ws.cell(a, 5).value = j
                ws.cell(a, 6).value = tools_pmc.vlookup(j, table_array_wlxxb, 2)
                ws.cell(a, 7).value = tools_pmc.vlookup(j, table_array_wlxxb, 3)
                a += 1
    for i in dict_tdlmx_t:
        for j in dict_tdlmx_t[i]:
            bool1 = True
            for k in dict_tdlmx_y:
                for l in dict_tdlmx_y[k]:
                    if i == k and j == l:
                        bool1 = False
            if bool1:
                ws.cell(a, 1).value = "替代关系增加"
                ws.cell(a, 2).value = i
                ws.cell(a, 3).value = tools_pmc.vlookup(i, table_array_wlxxb, 2)
                ws.cell(a, 4).value = tools_pmc.vlookup(i, table_array_wlxxb, 3)
                ws.cell(a, 5).value = j
                ws.cell(a, 6).value = tools_pmc.vlookup(j, table_array_wlxxb, 2)
                ws.cell(a, 7).value = tools_pmc.vlookup(j, table_array_wlxxb, 3)
                a += 1

    ws = wb["替代料库存看板"]  # 替代料库存趋势表
    a = ws.max_row
    while ws.cell(a, 1).value != tools_pmc.today_str3:
        date = datetime.datetime.strptime(ws.cell(a, 1).value, "%Y/%m/%d")
        a += 1
        ws.cell(a, 1).value = (date + datetime.timedelta(days=1)).strftime("%Y/%m/%d")
        for i in range(2, 7):
            ws.cell(a, i).value = ws.cell(a - 1, i).value

    ws.cell(a, 2).value = dict_temp["all"]
    ws.cell(a, 2).number_format = numbers.BUILTIN_FORMATS[1]
    ws.cell(a, 3).value = (float(ws.cell(a, 2).value) - 3705958.636) / 3705958.636
    ws.cell(a, 3).number_format = numbers.BUILTIN_FORMATS[10]
    ws.cell(a, 4).value = dict_temp["优先消耗"]
    ws.cell(a, 4).number_format = numbers.BUILTIN_FORMATS[1]
    ws.cell(a, 5).value = (float(ws.cell(a, 4).value) - 52057) / 52057
    ws.cell(a, 5).number_format = numbers.BUILTIN_FORMATS[10]
    ws.cell(a, 6).value = dict_temp["num"]

    list1 = []
    list2 = []
    for i in range(135, ws.max_row + 1):
        list1.append(ws.cell(i, 2).value)
        list2.append(ws.cell(i, 4).value)
    list3 = range(1, len(list1) + 1)
    y1 = np.array(list1)
    x1 = np.array(list3)

    x = sm.add_constant(x1)
    regression1 = sm.OLS(y1, x)
    model1 = regression1.fit()
    y2 = model1.params[0] + model1.params[1] * x1

    plt.figure()
    plt.plot(x1, y1, label='替代料库存')
    plt.plot(x1, y2, label='线性回归线')
    plt.legend()
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams["axes.unicode_minus"] = False
    plt.title("替代料库存走势图")
    plt.savefig("//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/1.png")

    png_file = Image("//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/1.png")
    # noinspection PyProtectedMember
    del ws._images[0]
    ws.add_image(png_file, "J135")

    str1 = str(model1.summary())
    list1 = str1.split("\n")
    a = 163
    for i in list1:
        ws.cell(a, 10).value = i
        a += 1



    wb.save(
        "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/" + tools_pmc.today_str1 + "替代料管理看板.xlsx")
    try:
        shutil.copy(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/" + tools_pmc.today_str1 + "替代料管理看板.xlsx",
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理")
    except PermissionError:
        pass

    try:
        os.remove(
            "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/" + tools_pmc.yesterday_str1 + "替代料管理看板.xlsx")
    except FileNotFoundError:
        pass
    except PermissionError:
        pass

    print(tools_pmc.today_str1 + ":tdl succeed!")
    return dict_tdlmx_t_pro


# tdl(tools_pmc.table_array_wlxxb)
